﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;

/// <summary>
/// 数据访问层DAL
/// sql帮助工具类
/// </summary>

//在DAL层，系统使用在业务逻辑层BLL中形成的相应登录sql命令去数据库中执行，并将结果（即相应的用户名及密码的用户是否存在）返回出来，因此此层首先就需要连接数据库
namespace DAL
{
    public class sqlHelper
    {
        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns></returns>
        private static MySqlConnection CreateConnection()
        {
            //第一步 新建数据库连接对象
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = @"server=localhost;
                                     port=3306;
                                     database=bookmanage;
                                     user=test;
                                     password=test1234;
                                     charset=utf8";
            return con;
        }
        
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string ExcuteScalar(string sql, MySqlParameter[] parameters)
        {
            string result = "";
            //第二步 新建一个用于执行sql语句的命令对象
            MySqlCommand cmd = new MySqlCommand();
            //这里使用try{}catch{}语块来对数据库连接等异常作出反应提示
            try
            {
                cmd.Connection = CreateConnection();//制定命令对象所用的连接
                cmd.Connection.Open();
                cmd.CommandText = sql;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);//添加sql参数数据到cmd对象的parameters集合中
                //第三步 执行sql语句获取返回结果
                result = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                result = "-1";//如果出现连接异常则将result设置为-1，则业务逻辑层BLL的return返回结果为false
            }
            finally
            {
                //第四步 使用完连接对象后关闭连接
                cmd.Connection.Close();
            }
            return result;
        }
        
        /// <summary>
        /// 图书信息页面显示
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="startRow"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public static DataTable ExcuteTable(string sql,int startRow,int pageCount)
        {
            MySqlCommand cmd = new MySqlCommand();//新建一个用于执行sql语句的命令对象
            DataTable result = null;
            try
            {
                cmd.Connection = CreateConnection();//制定命令对象所用的连接
                cmd.CommandText = sql;
                cmd.Connection.Open();
                MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();//数据集
                sda.Fill(ds,startRow,pageCount,"bookList");
                result = ds.Tables["bookList"];
            }
            catch
            { }
            finally
            {
                cmd.Connection.Close();
            }
            return result;
        }
        
        /// <summary>
        /// 增删改及用户注册四种功能
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExcuteNonquery(string sql, MySqlParameter[] parameters)
        {
            MySqlCommand cmd = new MySqlCommand();//新建一个用于执行sql语句的命令对象
            int result = 0;
            try
            {
                cmd.Connection = CreateConnection();//制定命令对象所用的连接
                cmd.CommandText = sql;
                cmd.Connection.Open();
                if (parameters != null)
                cmd.Parameters.AddRange(parameters);
                result = cmd.ExecuteNonQuery();
            }
            catch
            { }
            finally
            {
                cmd.Connection.Close();
            }
            return result;
        }

        /// <summary>
        /// 查询图书
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExcuteTable(string sql,MySqlParameter[] parameters)
        {
            MySqlCommand cmd = new MySqlCommand();//新建一个用于执行sql语句的命令对象
            DataTable result = null;
            try
            {
                cmd.Connection = CreateConnection();//制定命令对象所用的连接
                cmd.CommandText = sql;
                cmd.Connection.Open();
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();//数据集
                sda.Fill(ds,"bookList");
                result = ds.Tables["bookList"];
            }
            catch
            { }
            finally
            {
                cmd.Connection.Close();
            }
            return result;
        }

        public static int GetRoleId(string sql, MySqlParameter[] parameters)
        {
            int result = 0;
            //第二步 新建一个用于执行sql语句的命令对象
            MySqlCommand cmd = new MySqlCommand();
            //这里使用try{}catch{}语块来对数据库连接等异常作出反应提示
            try
            {
                cmd.Connection = CreateConnection();//制定命令对象所用的连接
                cmd.Connection.Open();
                cmd.CommandText = sql;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);//添加sql参数数据到cmd对象的parameters集合中
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    result = Convert.ToInt32(dr["RoleId"]);
                }
            }
            catch
            {
            }
            finally
            {
                //第四步 使用完连接对象后关闭连接
                cmd.Connection.Close();
            }
            return result;
        }
    }
}
